package com.edu.dao;

import com.edu.bean.Feedback;
import com.edu.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FeedbackDao {
    
    /**
     * 分页查询反馈
     */
    public List<Feedback> findByPage(String title, int start, int pageSize) {
        StringBuilder sql = new StringBuilder(
                "SELECT f.*, u.username FROM feedback f " +
                        "LEFT JOIN user u ON f.user_id = u.user_id"
        );
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql.append(" WHERE f.title LIKE ?");
            params.add("%" + title.trim() + "%");
        }

        sql.append(" ORDER BY f.feedback_id DESC LIMIT ?, ?");
        params.add(start);
        params.add(pageSize);

        List<Feedback> feedbackList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                feedbackList.add(convertToFeedback(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return feedbackList;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String title) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM feedback");
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql.append(" WHERE title LIKE ?");
            params.add("%" + title.trim() + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 根据ID查询反馈
     */
    public Feedback findById(Integer feedbackId) {
        String sql = "SELECT f.*, u.username FROM feedback f " +
                "LEFT JOIN user u ON f.user_id = u.user_id " +
                "WHERE f.feedback_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, feedbackId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToFeedback(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 将ResultSet转换为Feedback对象
     */
    private Feedback convertToFeedback(ResultSet rs) throws SQLException {
        Feedback feedback = new Feedback();
        feedback.setFeedbackId(rs.getInt("feedback_id"));
        feedback.setUserId(rs.getInt("user_id"));
        feedback.setTitle(rs.getString("title"));
        feedback.setContent(rs.getString("content"));
        feedback.setCreateTime(rs.getTimestamp("create_time"));
        feedback.setUsername(rs.getString("username"));
        return feedback;
    }

    /**
     * 添加反馈
     */
    public boolean add(Feedback feedback) {
        String sql = "INSERT INTO feedback (user_id, title, content, create_time) VALUES (?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, feedback.getUserId());
            stmt.setString(2, feedback.getTitle());
            stmt.setString(3, feedback.getContent());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 用户端添加反馈
     */
    public boolean userAddFeedback(Feedback feedback) {
        String sql = "INSERT INTO feedback (user_id, title, content, create_time) VALUES (?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, feedback.getUserId());
            stmt.setString(2, feedback.getTitle());
            stmt.setString(3, feedback.getContent());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 用户端分页查询反馈列表
     */
    public List<Feedback> userFindByPage(int start, int pageSize) {
        String sql = "SELECT f.*, u.username, u.real_name FROM feedback f " +
                "LEFT JOIN user u ON f.user_id = u.user_id " +
                "ORDER BY f.create_time DESC LIMIT ?, ?";

        List<Feedback> feedbackList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, start);
            stmt.setInt(2, pageSize);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                feedbackList.add(convertToFeedback(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return feedbackList;
    }

    /**
     * 用户端查询反馈总数
     */
    public int userFindTotalCount() {
        String sql = "SELECT COUNT(*) FROM feedback";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}